package myjdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @Auther: cty
 * @Date: 2020/1/21 19:31
 * @Description:  测试ORM 封装一条记录：Map，封装多条记录：List<Map>、Map<String,Map>
 * @version: 1.0
 */
public class JDBC_Demo10_SORM02 {
    //使用Map封装一条数据记录
    public static void test01(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String,Object> row = new HashMap<String, Object>(); //使用一个Map封装一条记录

        try {
            //1 加载数据库驱动  2 获取数据库连接
            conn = JDBCUtil.getMysqlConn();

            //3 操作数据库——查询一条数据记录
            ps = conn.prepareStatement("select empname,salary,age from emp where id=?");
            ps.setObject(1, 1);
            rs = ps.executeQuery();

            //4 处理返回数据——将返回的一条记录封装到一个Map对象
            while(rs.next()){
//				System.out.println(rs.getString(1)+"--"+rs.getDouble(2)+"--"+rs.getInt(3));
                row.put("empname", rs.getObject(1));
                row.put("salary", rs.getObject(2));
                row.put("age", rs.getObject(3));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //5 关闭连接
            JDBCUtil.close(rs, ps, conn);
        }

        //遍历Map，就是遍历这一行的多列的信息
        for(String key:row.keySet()){
            System.out.print(key+"--"+row.get(key)+"\t");
        }
        System.out.println();
    }

    //使用List<Map>封装多条数据记录
    public static void test02(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> list= new ArrayList<Map<String,Object>>();

        try {
            //1 加载数据库驱动  2 获取数据库连接
            conn = JDBCUtil.getMysqlConn();

            //3 操作数据库——查询符合条件的数据记录
            ps = conn.prepareStatement("select empname,salary,age from emp where id>?");
            ps.setObject(1, 1);
            rs = ps.executeQuery();

            //4 处理返回数据——将返回的多条数据记录封装到List<Map>
            while(rs.next()){
//				System.out.println(rs.getString(1)+"--"+rs.getDouble(2)+"--"+rs.getInt(3));
                Map<String,Object> row = new HashMap<String, Object>(); //使用一个Map封装一条记录
                row.put("empname", rs.getObject(1));
                row.put("salary", rs.getObject(2));
                row.put("age", rs.getObject(3));

                list.add(row);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //5 关闭连接
            JDBCUtil.close(rs, ps, conn);
        }

        //遍历List<Map>
        for(Map<String,Object> row:list){
            for(String key:row.keySet()){
                System.out.print(key+"--"+row.get(key)+"\t");
            }
            System.out.println();
        }
    }

    //使用Map<String,Map>封装多条数据记录
    public static void test03(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String,Map<String,Object>> maps = new HashMap<String,Map<String,Object>>();

        try {
            //1 加载数据库驱动  2 获取数据库连接
            conn = JDBCUtil.getMysqlConn();

            //3 操作数据库——查询符合条件的数据记录
            ps = conn.prepareStatement("select empname,salary,age from emp where id>?");
            ps.setObject(1, 1);
            rs = ps.executeQuery();

            //4 处理返回数据——将返回的多条数据记录封装到List<Map>
            while(rs.next()){
//				System.out.println(rs.getString(1)+"--"+rs.getDouble(2)+"--"+rs.getInt(3));
                Map<String,Object> row = new HashMap<String, Object>(); //使用一个Map封装一条记录
                row.put("empname", rs.getObject(1));
                row.put("salary", rs.getObject(2));
                row.put("age", rs.getObject(3));

                maps.put(rs.getString(1), row);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //5 关闭连接
            JDBCUtil.close(rs, ps, conn);
        }

        //遍历Map<String,Map>
        for(String empname:maps.keySet()){
            Map<String,Object> row = maps.get(empname);
            for(String key:row.keySet()){
                System.out.print(key+"--"+row.get(key)+"\t");
            }
            System.out.println();
        }
    }


    public static void main(String[] args) {
        test01();
        System.out.println("----------------------------------------");
        test02();
        System.out.println("----------------------------------------");
        test03();
    }
}

/* res:
empname--张三	salary--30000.0	age--3
----------------------------------------
empname--李四	salary--40000.0	age--4
empname--王五	salary--50000.0	age--5
----------------------------------------
empname--李四	salary--40000.0	age--4
empname--王五	salary--50000.0	age--5
*/
